package Chat;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import Entity.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

import javax.print.DocFlavor.INPUT_STREAM;

import com.lowagie.text.pdf.codec.Base64.InputStream;

public class mysqlConnection {

	private Statement stmt;
	private ResultSet rs = null;
	private Object result;
	private Connection conn;

	public mysqlConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception ex) {/* handle the error */
		}

		try {
			conn = DriverManager.getConnection("jdbc:mysql://localhost/katom","root", "12345");
			System.out.println("SQL connection succeed");
		} catch (SQLException ex) {/* handle any errors */
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
		try {
			stmt = conn.createStatement();
		} catch (Exception ex) {
		}
	}

	public Object loginQuery(User u) {
		try {
			rs = stmt.executeQuery("select * from user where UserID='"
					+ u.getUserName() + "'");
			if (rs.next()) {
				if (u.getPassword().equals(rs.getString("Password"))) {
					u.setPrivilage(rs.getString("Privilage"));
					u.setIs_exists(true);
				} else {
					u.setPassword("");
				}

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return u;
	}

	/**
	 * this method changes user password
	 * 
	 * @param u
	 *            is the user details with the new password
	 * @return true if update password succeed and false otherwise
	 */
	public Object changePassQuery(User u) {
		
		try {
			boolean ans = (boolean) checkUserExsistsQuery(String.valueOf(u.getUserName()));
			if(ans){
			stmt.executeUpdate("update user set password='" + u.getPassword()
					+ "' where UserID='" + u.getUserName() + "'");
			}
			else return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;

	}

	/**
	 * this method check if user exists in DB
	 * 
	 * @param user
	 *            details
	 * @return the name (private & last name) of the user
	 */
	public Object checkUserExsistsQuery(String userID) {
		PreparedStatement preparedStatement;
		try {
			preparedStatement = conn.prepareStatement("select * from user where userID = ?");
			preparedStatement.setString(1, userID);
			rs = preparedStatement.executeQuery();
			if (rs.next())
				return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * this method first checks if user name given already exists and if not,
	 * added new worker to DB,
	 * 
	 * @param u
	 *            is user entity that contain the new worker details
	 * @return true if added new worker succeed and false otherwise
	 */
	public Object addWorkerQuery(User u) {
		boolean ans = (boolean) checkUserExsistsQuery(u.getUserName()
				.toString());
		if (!ans) {
			try {
				stmt.executeUpdate("insert into user (userID,Password,Privilage) values "
						+ "('"+ u.getUserName()+ "','"+ u.getPassword()+ "','" + u.getPrivilage() + "')");
				stmt.executeUpdate("insert into worker (wID,fName,LName,role,phonenNumber,address,Email) values "
						+ "('"+ u.getUserName()+ "','"+ u.getName()+ "','" + u.getLName() + "','"+ u.getRole()+ "','"+ u.getPhoneNum()+ "','" + u.getAddress() + "','"+ u.getEmail() + "')");

			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
			return true;
		}

		return false;
	}

	/**
	 * this method update worker detais in the DB,
	 * 
	 * @param u
	 *            is user entity that contain the new worker details
	 * @return true if updated worker details succeed and false otherwise
	 */
	public Object updateworkerdetailsQuery(User u) {
		try {
			stmt.executeUpdate("update worker set fName='" + u.getName()+ "',LName='" + u.getLName() + "',phonenNumber='"
				+ u.getPhoneNum() + "', address='" + u.getAddress()+ "',Email='" + u.getEmail() + "' where wID='"+ u.getUserName() + "'");
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * this method get customer details from the DB,
	 * 
	 * @param custID
	 *            is the customer ID to get hid details
	 * @return c- entity of type customer with his details
	 */
	public Object viewCustomerDetailsQuery(Integer custID) {
		Customer c = new Customer();
		try {
			rs = stmt.executeQuery("select * from customer where cID = "
					+ custID);
			if (rs.next()) {
				c.setcID(custID);
				c.setAddress(rs.getString("address"));
				c.setgFname(rs.getString("gFname"));
				c.setbFname(rs.getString("bFname"));
				c.setgLname(rs.getString("gLname"));
				c.setbLname(rs.getString("bLname"));
				c.setgPhone(rs.getString("gPhoneNumber"));
				c.setbPhone(rs.getString("bPhoneNumber"));
				c.setEmail(rs.getString("Email"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return c;
	}
	
	/**
	 * this method get customer details from the DB,
	 * 
	 * @param custID
	 *            is the customer ID to get hid details
	 * @return c- entity of type customer with his details
	 */
	public Object getAllCustomerDetailsQuery() {
		ArrayList<Customer> allCustoner = new ArrayList<>();
		try {
			rs = stmt.executeQuery("select * from customer");
			while (rs.next()) {
				Customer c = new Customer();
				c.setcID(rs.getInt("cID"));
				c.setAddress(rs.getString("address"));
				c.setgFname(rs.getString("gFname"));
				c.setbFname(rs.getString("bFname"));
				c.setgLname(rs.getString("gLname"));
				c.setbLname(rs.getString("bLname"));
				c.setgPhone(rs.getString("gPhoneNumber"));
				c.setbPhone(rs.getString("bPhoneNumber"));
				c.setEmail(rs.getString("Email"));
				allCustoner.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return allCustoner;
	}
	/**
	 * this method get worker details from the DB,
	 * 
	 * @param custID
	 *            is the worker ID to get hid details
	 */
	public Object getWorkerDetailsQuery(Integer wId) {
		User u = new User();
		System.out.println("in sql");
		try {
			rs = stmt.executeQuery("select * from worker where wID = '" + wId
					+ "' ");
			if (rs.next()) {
				System.out.println("in if");
				u.setUserName(rs.getInt("wID"));
				u.setName(rs.getString("fName"));
				u.setLName(rs.getString("lName"));
				u.setAddress(rs.getString("address"));
				u.setEmail(rs.getString("Email"));
				u.setRole(rs.getString("role"));
				u.setPhoneNum(rs.getString("phonenNumber"));
			} else {
				u.setUserName(0);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return u;
	}

	/**
	 * this method set new worker's permission in the DB,
	 * 
	 * @param u
	 *            entity of type user with the worker details
	 * @return true if the permission change successfully and false otherwise
	 */
	public Object setPermissionLevelQuery(User u) {
		try {
			stmt.executeUpdate("update user set Privilage ='"+ u.getPrivilage() + "' where userID = '" + u.getUserName()+ "'");
			if(!(u.getRole().equals("")))
				stmt.executeUpdate("update worker set role ='"+ u.getRole() + "' where wID = '" + u.getUserName()+ "'");
			else
				stmt.executeUpdate("update worker set role ='"+ u.getPrivilage() + "' where wID = '" + u.getUserName()+ "'");
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}

		return true;

	}

	/**
	 * this method remove worker from DB,
	 * 
	 * @param wID
	 *            is the worker ID to remove
	 ** @return true if remove worker succeed and false otherwise
	 */
	public Object removeWorkerQuery(String wID) {
		try {
			stmt.executeUpdate("DELETE FROM user WHERE userID=" + wID);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * this method gets all worker's first name exists in DB
	 ** 
	 * @return an array list with all the worker first name
	 */
	public Object getWorkersNameQuery() {
		ArrayList<String> wNameList = new ArrayList<String>();
		try {
			rs = stmt
					.executeQuery("select DISTINCT fName from user,worker where userID=wID and Privilage = 'worker'");
			while (rs.next()) {
				wNameList.add(rs.getString("fName"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return wNameList;
	}

	/**
	 * this method gets worker last name
	 * 
	 * @param wFname
	 *            is worker first name
	 ** @return a list with all workers last name
	 */
	public Object getWorkerLastNameQuery(String wFname) {
		ArrayList<String> wLName = new ArrayList<>();
		try {
			rs = stmt
					.executeQuery("select DISTINCT LName from worker where fName='"
							+ wFname + "' ");
			while (rs.next()) {
				wLName.add(rs.getString("LName"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return wLName;
	}

	/**
	 * this method gets worker ID
	 * 
	 * @param wName
	 *            is worker first and last name
	 ** @return a list with all workers id
	 */
	public Object getWorkerIDQuery(String wName) {
		ArrayList<String> wID = new ArrayList<>();
		try {
			String[] name = wName.split(" ");
			rs = stmt.executeQuery("select wID from worker where fName='"
					+ name[0] + "' and LName='" + name[1] + "'");
			while (rs.next()) {
				wID.add(rs.getString("wID"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return wID;
	}

	/**
	 * this method gets worker privilege
	 * 
	 * @param wFname
	 *            is worker id
	 ** @return the worker privilege
	 */
	public Object getWorkerPermissionQuery(Integer wId) {
		String per = new String();
		try {
			rs = stmt.executeQuery("select * from user where userID='" + wId
					+ "'");
			if (rs.next()) {
				per = rs.getString("Privilage");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return per;
	}

	/**
	 * this method change event status to closed
	 * 
	 * @param e
	 *            is entity that contain the customer id and date of event to
	 *            closed
	 ** @return true if event closed succeed and false otherwise
	 */
	public Object closeEventQuery(Event e) {
		try {
			rs=stmt.executeQuery("select * from event where cId='"+ e.getCustomerID()
					+ "' and eDate='"+ e.getEvetDate()+ "' and isPaied='1' ");
			if(rs.next())
				stmt.executeUpdate("update event set eStatus = 'CLOSED' where cId='"+ e.getCustomerID()
					+ "' and eDate='"+ e.getEvetDate()+ "' ");
			else
				return false;
		} catch (SQLException e1) {
			e1.printStackTrace();
			}
		return true;
	}

	/**
	 * this method get details of the customers and there meeting 
	 * 
	 ** @return custMeetingList is an array list with all the meeting requested
	 */
	public Object getCustomersMeetingQuery(){ 
		ArrayList<Meeting> custMeetingList = new ArrayList<Meeting>();
		try {
			rs = stmt.executeQuery("select * from meeting,customer,worker,event where worker.wid=meeting.managerIncharge " +
					"and customer.cID=meeting.cID and customer.cID=event.cID and mDate>=current_date()");
			while (rs.next()) {
				Meeting m = new Meeting();
				m.setcID(rs.getInt("cID"));
				m.setbName(rs.getString("bFname")+" "+rs.getString("bLname"));
				m.setgName(rs.getString("gFname")+" "+rs.getString("gLname"));
				m.setcAddress(rs.getString("address"));
				m.seteDate(rs.getString("eDate"));
				m.setmDate(rs.getString("mDate"));
				m.setmTime(rs.getString("mTime"));
				m.setePlace(rs.getString("ePlace"));
				m.setManagerIncharge(rs.getString("fName")+" "+rs.getString("LName"));
				custMeetingList.add(m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return custMeetingList;
	}
	/**
	 * this method get details of the customers and there meeting if the status of their event is open or whitin to be open
	 * 
	 ** @return custMeetingList is an array list with all the meeting requested
	 */
	public Object getMeetingQuery(){ 
		ArrayList<Meeting> custMeetingList = new ArrayList<Meeting>();
		try {
			rs = stmt.executeQuery("select * from meeting,customer,worker,event where worker.wid=meeting.managerIncharge " +
					"and customer.cID=meeting.cID and customer.cID=event.cID and (eStatus='OPEN' OR eStatus='WAITING')");
			while (rs.next()) {
				Meeting m = new Meeting();
				m.setcID(rs.getInt("cID"));
				m.setbName(rs.getString("bFname")+" "+rs.getString("bLname"));
				m.setgName(rs.getString("gFname")+" "+rs.getString("gLname"));
				m.setcAddress(rs.getString("address"));
				m.seteDate(rs.getString("eDate"));
				m.setmDate(rs.getString("mDate"));
				m.setmTime(rs.getString("mTime"));
				m.setePlace(rs.getString("ePlace"));
				m.setManagerIncharge(rs.getString("fName")+" "+rs.getString("LName"));
				custMeetingList.add(m);
			}
			if(custMeetingList.isEmpty())
			{
				rs = stmt.executeQuery("select * from customer");
				while(rs.next()){
					Meeting m = new Meeting();
					m.setcID(rs.getInt("cID"));
					m.setbName(rs.getString("bFname")+" "+rs.getString("bLname"));
					m.setgName(rs.getString("gFname")+" "+rs.getString("gLname"));
					custMeetingList.add(m);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return custMeetingList;
	}
	/**
	 * this method get details of the customers and there meeting if the status of their event is open or whitin to be open
	 * 
	 ** @return custMeetingList is an array list with all the meeting requested
	 */
	public Object getCidsQuery(){ 
		ArrayList<Meeting> custMeetingList = new ArrayList<Meeting>();
		try {
			rs = stmt.executeQuery("select * from customer,worker,meeting where worker.wid=meeting.managerIncharge " +
					"and customer.cID=meeting.cID and customer.cID=event.cID");
			while (rs.next()) {
				Meeting m = new Meeting();
				m.setcID(rs.getInt("cID"));
				m.setbName(rs.getString("bFname")+" "+rs.getString("bLname"));
				m.setgName(rs.getString("gFname")+" "+rs.getString("gLname"));
				m.setcAddress(rs.getString("address"));
				m.setmDate(rs.getString("mDate"));
				m.setmTime(rs.getString("mTime"));
				m.setePlace(rs.getString("ePlace"));
				m.setManagerIncharge(rs.getString("fName")+" "+rs.getString("LName"));
				custMeetingList.add(m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return custMeetingList;
	}
	/**
	 * this method get all event dates for a given customer ID
	 * 
	 ** @return eDateList an array list with all the Dates
	 */
	public Object getEventDateForCustomerQuery(Integer cID) {
		ArrayList<String> eDateList = new ArrayList<String>();
		try {
			rs = stmt.executeQuery("select Date from event where cID = " + cID);
			while (rs.next()) {
				eDateList.add(rs.getString("Date"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return eDateList;
	}

	/**
	 * this method create new event in DB
	 * 
	 * @param event
	 *            is entity that contain the new event details
	 ** @return true if event created succeed and false otherwise
	 */
	public Object createEventQuery(Event event) {

		try {
			stmt.executeUpdate("update event set ePrice='"+event.getEventPricing()+"',eTime='"+
					event.geteTime()+"',eStatus='OPEN' where cID='"+event.getCustomerID()+"' and " +
							"eDate='"+event.getEvetDate()+"' and ePlace='"+event.getEvetPlace()+"'");
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	/**
	 * this method check if event exist in DB
	 * 
	 * @param event is entity that contain the event details
	 ** @return true if event exist  and false otherwise
	 */
	public Object isEventExistQuery(Event event){
		try {
			System.out.println(event.getEvetDate());
			System.out.println(event.getEvetPlace());
			rs=stmt.executeQuery("select * from event where eDate='"+event.getEvetDate()+"' and cID='"+event.getCustomerID()+"' ");
			if(rs.next())
				if(rs.getString("eStatus").equals("OPEN"))
					return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	
	}
	/**
	 * this method check if event open 
	 * 
	 * @param event is entity that contain the event details
	 ** @return true if event is open  and false otherwise
	 */
	public Object isEventOpenQuery(Event event){
		try {
			System.out.println(event.getEvetDate());
			rs=stmt.executeQuery("select * from event where eDate='"+event.getEvetDate()+"' " +
					"and cID='"+event.getCustomerID()+"' and eStatus='OPEN' ");
			if(rs.next())
				return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	/**
	 * this method cancel event
	 * 
	 * @param event
	 *            is entity that contain the event details that needs to be
	 *            canceled
	 ** @return true if event canceled succeed and false otherwise
	 */
	public Object cancelEventQuery(Event event) {
		try {
			stmt.executeUpdate("DELETE FROM event WHERE cID='"+ event.getCustomerID() + "' and eDate ='"
					+ event.getEvetDate() + "'");
			stmt.executeUpdate("DELETE FROM meeting WHERE cID='"+ event.getCustomerID() + "' and eDate ='"
					+ event.getEvetDate() + "'");
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * this method edit event details
	 * 
	 * @param event
	 *            is entity that contain the event details that needs to be edit
	 ** @return true if event was edit successfully and false otherwise
	 */
	public Object editEventQuery(Event event) {
		try {
			rs = stmt.executeQuery("select * from event where cId = '"+ event.getCustomerID() + "' and eDate='"
					+ event.getEvetDate() + "'");
			if (rs.next()) {
				if (rs.getInt("isPaied") == 1)
					return 1;
				else if (rs.getString("ePlace").equals(event.getEvetPlace()))
					return 2;
				else if (rs.getString("eDate").equals(event.getEvetNewDate()))
					return 3;
				else if (rs.getString("ePrice").equals(event.getEventPricing()))
					return 4;
				else if (rs.getString("eTime").equals(event.geteTime()))
					return 6;
			}
			if (event.isPaied()){
				stmt.executeUpdate("update event set isPaied = '1' where cId = '"
						+ event.getCustomerID()+ "' and eDate='"+ event.getEvetDate() + "'");
				System.out.println("ispaied");
			}
			if (!(event.getEvetPlace().equals(""))){
				stmt.executeUpdate("update event set ePlace ='"	+ event.getEvetPlace() + "' where cId = '"
					+ event.getCustomerID() + "' and eDate='"+ event.getEvetDate() + "'");
			System.out.println("place");
			}
			if (!(event.getEventPricing() == 0)){
				stmt.executeUpdate("update event set ePrice ='"+ event.getEventPricing() + "' where cId = '"
						+ event.getCustomerID() + "' and eDate='"+ event.getEvetDate() + "'");
				System.out.println("price");
			}
			if (!(event.geteTime().equals(""))){
				stmt.executeUpdate("update event set eTime ='"+ event.geteTime() + "' where cId = '"
						+ event.getCustomerID() + "' and eDate='"+ event.getEvetDate() + "'");
				System.out.println("time");
			}
			if (!(event.getEvetNewDate().equals(""))){
				System.out.println(event.getEvetNewDate());
				stmt.executeUpdate("update event set eDate ='"+ event.getEvetNewDate() + "' where cId = '"
						+ event.getCustomerID() + "' and eDate='"+ event.getEvetDate() + "'");
				System.out.println("new Dateeeeeeee");
				stmt.executeUpdate("update meeting set eDate ='"+ event.getEvetNewDate() + "' where cId = '"
						+ event.getCustomerID() + "' and eDate='"+ event.getEvetDate() + "'");
				System.out.println("update in meeting");
				System.out.println("enewDate");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
		System.out.println("returrnnnnn from sql edit event");
		return 5;
	}

	/**
	 * this method coordinatingMeeting to a potential customer
	 * 
	 * @param m
	 *            is entity that contain the meeting details
	 ** @return true if meeting was coordinating successfully and false otherwise
	 */
	public Object coordinatingMeetingQuery(Meeting m) {
		String[] a = m.getManagerIncharge().split(" ");
		int managetID =0;
		String cName=null;
		try {
	
			rs = stmt.executeQuery("select wID from worker where fName='"+ a[0] + "' and LName='" + a[1] + "'");		
			if(rs.next())
				managetID = rs.getInt("wID");
			System.out.println("managetID "+managetID);
			rs = stmt.executeQuery("select * from customer where cID = '"+ m.getcID() + "'");
			if(rs.next()){
				 cName = rs.getString("gFname") + " "+ rs.getString("gLname");
			}
			rs = stmt.executeQuery("select * from meeting where cID = '"+ m.getcID() + "' and eDate= '"+ m.geteDate() + "'");
			if(rs.next()) 
			{
				System.out.println("in ifff");
				int meetingCnt =  rs.getInt("numOfMeeting") + 1;
				stmt.executeUpdate("update meeting set numOfMeeting='"+meetingCnt + "' where cID='"
						+ m.getcID() + "' and eDate='" + m.geteDate() + "'");
				
				if(!(m.getManagerIncharge().equals(managetID)))
					stmt.executeUpdate("update meeting set managerIncharge='"+m.getManagerIncharge() + "' where cID='"
							+ m.getcID() + "' and eDate='" + m.geteDate() + "'");
				
				if(!(m.getmDate().equals(rs.getString("mDate"))))
					stmt.executeUpdate("update meeting set mDate='"+m.getmDate() + "' where cID='"
							+ m.getcID() + "' and eDate='" + m.geteDate() + "'");	
				
				if(!(m.getmTime().equals(rs.getString("mTime"))))
					stmt.executeUpdate("update meeting set mTime='"+m.getmTime() + "' where cID='"
							+ m.getcID() + "' and eDate='" + m.geteDate() + "'");
			}
			
		
		else{
			System.out.println("inserttt");
			stmt.executeUpdate("insert into event(cID,eDate,ePlace) values('"
					+ m.getcID()+ "','"+ m.geteDate()+ "','"+ m.getePlace()+ "')");
				stmt.executeUpdate("insert into meeting(cID,eDate,mDate,managerIncharge,mTime) values('"
					+ m.getcID()+ "','"+ m.geteDate()+ "','"+ m.getmDate()+ "','"+ managetID+ "','"+ m.getmTime() + "')");
				System.out.println("insert222");
				
				System.out.println("ottt from sql");
		}

			
		} catch (SQLException e) {
			e.printStackTrace();
			return "2";
		}
		return cName;
	}

	/**
	 * this method add new customer to a DB
	 * 
	 * @param c
	 *            is entity that contain the new customer details
	 ** @return true if customer was add successfully and false otherwise
	 * 
	 */
	public Object addCustomerQuery(Customer c) {
		try {
			
			rs = stmt.executeQuery("select * from customer where cID='"+c.getcID()+"'");		
			if(rs.next())
				return false;
			stmt.executeUpdate("insert into customer(cID,gFname,gLname,gPhoneNumber,address,Email,bFname,bLname,bPhoneNumber) "
					+ "values('"+ c.getcID()+ "','"+ c.getgFname()+ "','"+ c.getgLname()+ "','"+ c.getgPhone()+ "','"+ c.getAddress()+ "','"+ c.getEmail()+"','"+ c.getbFname()+ "','"+ c.getbLname()+ "','"+ c.getbPhone() + "')");
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return true;
	}

	/**
	 * This method change user's status to logout
	 * 
	 * @param userId
	 *            is the user's id that needs to logout
	 * @return true if user logout successfully from the system and false
	 *         otherwise
//	 */
//	public Object logOutQuery(Integer userId) {
//		try {
//			stmt.executeUpdate("update user set isLogged ='0' where userID='"
//					+ userId + "'");
//		} catch (SQLException e) {
//			e.printStackTrace();
//			return false;
//		}
//		return true;
//	}

	/**
	 * This method check the number of event a specific have
	 * 
	 * @param cID
	 *            is the customer id
	 * @return true if customer has only one event and false otherwise
	 */
	public Object getCustomerNumOfEventsQuery(Integer cID) {
		try {
			rs = stmt
					.executeQuery("select count(cId) as cnt from event where cId = '"
							+ cID + "'");
			if (rs.next()) {
				if (rs.getInt("cnt") == 1) {
					System.out.println(rs.getInt("cnt"));
					return true;
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * This method all different event's date exist in DB and according to this
	 * all the customers ID relevant
	 * 
	 * @return a Hash map which the key is the event date and the value an array
	 *         with all customer's names
	 */
	public Object getDatesQuery() {

		HashMap<String, ArrayList<String>> cIdByDateList = new HashMap<>();
		
		try {
			rs = stmt.executeQuery("select eDate, GROUP_CONCAT(gFname,' and ',bFname) as g_bName from event,customer where event.cId=customer.cID  GROUP BY eDate");
			while (rs.next()) {
				String[] names = rs.getString("g_bName").split(",");
				// String [] fName = rs.getString("fName").split(",");
				ArrayList<String> g_bName = new ArrayList<String>();
				for (String x : names) {
					g_bName.add(x);
				}
				cIdByDateList.put(rs.getString("eDate"), g_bName);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cIdByDateList;
	}
	
	/**
	 * This method gets all different event's date exist in DB and according to this
	 * all the customers ID relevant
	 * 
	 * @return an array list with all the events Details
	 */
	public Object getEventDatesQuery() {

		ArrayList<Event> eventDateList = new ArrayList<>();
		try {
			rs = stmt.executeQuery("select event.ePlace,meeting.eDate,gFname,bFname,customer.cID from customer,meeting,event where " +
					"meeting.cID=customer.cID and customer.cID=event.cID ");
			while (rs.next()) {
				Event e = new Event();
				e.setCustomerID(rs.getInt("cID"));
				e.setEvetDate(rs.getString("eDate"));
				e.setEvetPlace(rs.getString("ePlace"));
				e.setG_b_names(rs.getString("gFname")+" and "+ rs.getString("bFname"));
				eventDateList.add(e);
				}
				
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return eventDateList;
	}


	/**
	 * This method gets all the manager names
	 * 
	 * @return a list with all the manager's name
	 */
	public Object getManagersNameQuery() {
		ArrayList<String> mNlist = new ArrayList<>();
		try {
			rs = stmt.executeQuery("select fName,LName from worker where role='Manager'");
			while (rs.next()) {
				mNlist.add(rs.getString("fName")+" "+rs.getString("LName"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return mNlist;
	}	
	/**
	 * This method change the manager incharge of a meeting 
	 * 
	 * @param  m is an entity that contain the meeting to change details
	 * @return true if update manager succeed and false otherwise
	 */
	public Object changeManagersInchargeQuery(Meeting m){
		try {
			String []s = m.getManagerIncharge().split(" ");
			rs= stmt.executeQuery("select * from worker where fname='"+s[0]+"' and Lname='"+s[1]+"' and role='manager'");
			if(rs.next())
				stmt.executeUpdate("update meeting set managerIncharge='"+rs.getInt("wID")+"' where mDate='"+m.getmDate()+"' and" +
						" mTime='"+m.getmTime()+"' and eDate='"+m.geteDate()+"'");
			else
				return false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return true;
	}

	/**
	 * This method gets event's details
	 * 
	 * @param event2
	 *            contain the event date and the event's owner (customerID)
	 * 
	 * @return event2 is an entity of event that contain event details
	 */
	public Object getEventDetailsQuery(Event event2) {
		try {
			rs = stmt.executeQuery("select * from event where cId='"
					+ event2.getCustomerID() + "' and eDate = '"
					+ event2.getEvetDate() + "'");
			if (rs.next()) {
				event2.setEventPricing(rs.getInt("ePrice"));
				event2.setEvetPlace(rs.getString("ePlace"));
				event2.setPaied(rs.getBoolean("isPaied"));
				event2.seteTime(rs.getString("eTime"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return event2;
	}

	/**
	 * This method update customer details in DB
	 * 
	 * @param customer
	 *            is a customer entity that contain the customer new details
	 *            that needs to update
	 * 
	 * @return @return true if customer details were updated successfully and
	 *         false otherwise
	 */
	public Object updateCustomerDetailsQuery(Customer customer) {
		PreparedStatement preparedStatement;
		try {
			rs = stmt.executeQuery("select * from customer where cID='"+customer.getcID()+"'");
			if(rs.next()){
				if(customer.getgFname().equals(rs.getString("gFname"))&&customer.getbFname().equals(rs.getString("bFname"))
					&&customer.getgLname().equals(rs.getString("gLname"))&&customer.getbLname().equals(rs.getString("bLname"))
					&&customer.getgPhone().equals(rs.getString("gPhoneNumber"))&&customer.getbPhone().equals(rs.getString("bPhoneNumber"))&&
				customer.getEmail().equals(rs.getString("Email"))&&customer.getAddress().equals(rs.getString("address")))
					return false;
			}
			preparedStatement = conn
					.prepareStatement("update customer set gFname=?, gLname=?, gPhoneNumber=?, address=?, Email=?,"
							+ " bFname=?, bLname=?, bPhoneNumber=? where cID=?");
			preparedStatement.setString(1, customer.getgFname());
			preparedStatement.setString(2, customer.getgLname());
			preparedStatement.setString(3, customer.getgPhone());
			preparedStatement.setString(4, customer.getAddress());
			preparedStatement.setString(5, customer.getEmail());
			preparedStatement.setString(6, customer.getbFname());
			preparedStatement.setString(7, customer.getbLname());
			preparedStatement.setString(8, customer.getbPhone());
			preparedStatement.setInt(9, customer.getcID());
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		System.out.println("ccc");
		return true;
	}

	/**
	 * this method remove worker from DB,
	 * 
	 * @param wID
	 *            is the worker ID to remove
	 ** @return true if remove worker succeed and false otherwise
	 */
	public Object removeCustomerQuery(Customer c) {
		try {
			System.out.println("in sql");
			stmt.executeUpdate("DELETE FROM customer WHERE gFname='="+c.getgFname()+"' and gLname='"+c.getgLname()+"' " +
					"and address='"+c.getAddress()+"' and bFname='"+c.getbFname()+"' and bLname='"+c.getbLname()+"'" );
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		System.out.println("out of sql");
		return true;
	}

	/**
	 * this method change customer's meeting details in DB
	 * 
	 * @param mToEdit
	 *            is a meeting entity to edit meeting details
	 ** @return true if edit meeting succeed and false otherwise
	 */
	public Object editMeetingQuery(Meeting mToEdit) {

		try {
			if (!(mToEdit.getmDate().equals("0000-00-00")))
				stmt.executeUpdate("update meeting set mDate='"+ mToEdit.getmDate() + "' WHERE cID='"
						+ mToEdit.getcID() +  "' and eDate='"+mToEdit.geteDate()+"'");
			if (!(mToEdit.getmTime().equals("00:00:00")))
				stmt.executeUpdate("update meeting set mTime='"+ mToEdit.getmTime() + "' WHERE cID='"
						+ mToEdit.getcID() + "' and eDate='"+mToEdit.geteDate()+"'");
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * this method get the work schedule from DB
	 * 
	 ** @return the work schedule in an array list
	 */
	public Object getScheduleQuery() {
		ArrayList<Schedule> tempSched = new ArrayList<>();
		try {
			rs = stmt.executeQuery("select * from schedule where Date>=current_DATE(); ");
			while (rs.next()) {
				Schedule s = new Schedule();
				s.setDate(rs.getString("Date"));
				s.setPlace(rs.getString("place"));
				s.setTeamLeader(rs.getString("teamLeader"));
				s.setStilsPhoto(rs.getString("stilsPhoto"));
				s.setvPhotografer1(rs.getString("vPhotografer1"));
				s.setvPhotografer2(rs.getString("vPhotografer2"));
				s.setNumOfVideoPhoto(rs.getInt("numOfVideoPhoto"));
				s.setHoverCam(rs.getString("hoverCam"));
				s.setMagnets(rs.getString("magnets"));
				s.setMagnetsPhoto1(rs.getString("magnetsPhoto1"));
				s.setMagnetsPhoto2(rs.getString("magnetsPhoto2"));
				s.setComments(rs.getString("comments"));
				s.setgContactName(rs.getString("contactName"));
				s.setgContactPhone(rs.getString("contactPhone"));
				tempSched.add(s);
			}
			for(Schedule s: tempSched){
				rs=stmt.executeQuery("select * from worker where wid='"+s.getTeamLeader()+"'");
				if(rs.next())
					s.setTeamLeader(rs.getString("fName")+" "+rs.getString("LName"));
				rs=stmt.executeQuery("select * from worker where wid='"+s.getStilsPhoto()+"'");
				if(rs.next())
					s.setStilsPhoto(rs.getString("fName")+" "+rs.getString("LName"));
				rs=stmt.executeQuery("select * from worker where wid='"+s.getvPhotografer1()+"'");
				if(rs.next())
					s.setvPhotografer1(rs.getString("fName")+" "+rs.getString("LName"));
				else
					s.setvPhotografer1("None");
				rs=stmt.executeQuery("select * from worker where wid='"+" "+s.getvPhotografer2()+"'");
				if(rs.next())
					s.setvPhotografer2(rs.getString("fName")+" "+rs.getString("LName"));
				rs=stmt.executeQuery("select * from worker where wid='"+s.getMagnetsPhoto1()+"'");
				if(rs.next())
					s.setMagnetsPhoto1(rs.getString("fName")+" "+rs.getString("LName"));
				else
					s.setMagnetsPhoto1("None");
				rs=stmt.executeQuery("select * from worker where wid='"+s.getMagnetsPhoto2()+"'");
				if(rs.next())
					s.setMagnetsPhoto2(rs.getString("fName")+" "+rs.getString("LName"));
				else
					s.setMagnetsPhoto2("None");
				
			}
		} catch (SQLException e) {
			e.printStackTrace();

		}
		return tempSched;
	}

	/**
	 * this method set a new row in the work schedule
	 ** 
	 ** @return true if insert to work schedule succeed
	 */
	public Object setScheduleQuery(Schedule schedule) {
		String[] name = null;
		try {
			//for team leader
			name=schedule.getTeamLeader().split(" ");
			rs= stmt.executeQuery("select wID from worker where fName='"+name[0]+"' and LName='"+name[1]+"'" );
			int tLID=0;
			if(rs.next())
				 tLID = rs.getInt("wID");
			stmt.executeUpdate("insert into workerinevent(wId, eventDate) values('"+tLID+"','"+schedule.getDate()+"')");
			//for stills
			name=schedule.getStilsPhoto().split(" ");
			rs= stmt.executeQuery("select wID from worker where fName='"+name[0]+"' and LName='"+name[1]+"'" );
			int sID=0;
			if(rs.next())
				 sID = rs.getInt("wID");
			stmt.executeUpdate("insert into workerinevent(wId, eventDate) values('"+sID+"','"+schedule.getDate()+"')");
			//for vidoe1
			name=schedule.getvPhotografer1().split(" ");
			rs= stmt.executeQuery("select wID from worker where fName='"+name[0]+"' and LName='"+name[1]+"'" );
			int v1ID=0;
			if(rs.next())
				v1ID = rs.getInt("wID");
			stmt.executeUpdate("insert into workerinevent(wId, eventDate) values('"+v1ID+"','"+schedule.getDate()+"')");
			stmt.executeUpdate("insert into schedule (Date,place,numOfVideoPhoto,hoverCam,magnets,contactName,contactPhone" +
					",teamLeader,stilsPhoto,vPhotografer1) values"
				+ "('"+ schedule.getDate()+ "','"+ schedule.getPlace()+ "','"+ schedule.getNumOfVideoPhoto()+ "','"
				+ schedule.isHoverCam()+ "','"+ schedule.isMagnets()+ "','"+ schedule.getbContactName()
				+ "','"+ schedule.getbContactPhone() + "','"+ tLID+ "','"+ sID+"','"+ v1ID+"')");

			//for comments
			if (!(schedule.getComments().equals("")))
				stmt.executeUpdate("update schedule set comments='"+ schedule.getComments()+"' where date='"+schedule.getDate()+"' and place='"+schedule.getPlace()+"'");
			//for magnets
			if (schedule.isMagnets().equals("YES"))
			{
				System.out.println("magnets yesss");
				name=schedule.getMagnetsPhoto1().split(" ");
				rs= stmt.executeQuery("select wID from worker where fName='"+name[0]+"' and LName='"+name[1]+"'" );
				int m1ID=0;
				if(rs.next())
				 m1ID = rs.getInt("wID");
				stmt.executeUpdate("update schedule set magnetsPhoto1='"+m1ID+"'" +
							" where date='"+schedule.getDate()+"' and place='"+schedule.getPlace()+"'");
				stmt.executeUpdate("insert into workerinevent(wId, eventDate) values('"+m1ID+"','"+schedule.getDate()+"')");

				name=schedule.getMagnetsPhoto2().split(" ");
				rs= stmt.executeQuery("select wID from worker where fName='"+name[0]+"' and LName='"+name[1]+"'" );
				int m2ID = 0;
				if(rs.next())
					m2ID = rs.getInt("wID");
				stmt.executeUpdate("update schedule set magnetsPhoto2='"+m2ID+"'" +
							" where date='"+schedule.getDate()+"' and place='"+schedule.getPlace()+"'");
				stmt.executeUpdate("insert into workerinevent(wId, eventDate) values('"+m2ID+"','"+schedule.getDate()+"')");
			}
			System.out.println("out of magnets");
			//for video2
			if (schedule.getNumOfVideoPhoto()==2)
			{
				name=schedule.getvPhotografer2().split(" ");
				rs= stmt.executeQuery("select wID from worker where fName='"+name[0]+"' and LName='"+name[1]+"'" );
				int v2ID=0;
				if(rs.next())
					v2ID = rs.getInt("wID");
				System.out.println("in numofv 2");
				stmt.executeUpdate("update schedule set vPhotografer2='"+v2ID+"'" +
					" where date='"+schedule.getDate()+"' and place='"+schedule.getPlace()+"'");
				stmt.executeUpdate("insert into workerinevent(wId, eventDate) values('"+v2ID+"','"+schedule.getDate()+"')");
			}
			System.out.println("out og v2");
		} catch (SQLException e) {
			e.printStackTrace();
			return false;

		}
		return true;
	}

	/**
	 * this method get the date of event that havn't scheduled yet
	 ** 
	 ** @return a list with the requested dates
	 */
	public Object getDateToScheduled() {
		ArrayList<String> dateList = new ArrayList<>();
		try {
			rs = stmt
					.executeQuery(" select DISTINCT eDate from event,schedule where event.eDate<>schedule.Date and schedule.place<>event.ePlace; ");
			while (rs.next())
				dateList.add(rs.getString("eDate"));

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return dateList;
	}

	/**
	 * this method get the event's places for a specific date that havn't been
	 * schedule yet from DB
	 ** 
	 ** @return a list with the requested dates
	 */
	public Object getEventPlaces(String date) {
		ArrayList<String> placeList = new ArrayList<>();
		try {
			rs = stmt
					.executeQuery("select ePlace from event,schedule where event.eDate = '"
							+ date + "'");
			while (rs.next())
				placeList.add(rs.getString("ePlace"));

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return placeList;
	}

	// /**
	// * this method get the event's contact details for a specific date that
	// havn't been schedule yet from DB
	// **
	// ** @return a list with the contact name and phone
	// */
	// public Object getEventContact(Schedule s){
	// try{
	// if(s.getContactName().equals("groom"))
	// rs =
	// stmt.executeQuery("select gFname as cFname,gLname as cLname,gPhoneNumber as cPhone from event,customer where event.cId=customer.cID and event.eDate='"+s.getDate()+"' and event.ePlace='"+s.getPlace()+"'");
	// else
	// rs =
	// stmt.executeQuery("select bFname as cFname,bLname as cLname,bPhoneNumber as cPhone from event,customer where event.cId=customer.cID and event.eDate='"+s.getDate()+"' and event.ePlace='"+s.getPlace()+"'");
	//
	// if(rs.next()){
	// s.setContactName(rs.getString("cFname")+" "+ rs.getString("cLname"));
	// s.setContactPhone(rs.getString("cPhone"));
	// }
	//
	//
	// }catch (SQLException e) {
	// e.printStackTrace();
	// }
	//
	// return s;
	// }
	/**
	 * this method gets the meeting of today to present the manager incharge
	 * when he login
	 ** 
	 ** @return a list with the manager's massages
	 */
	public Object getMeetingMassage(Integer managerID) {

		ArrayList<Massage> msgList = new ArrayList<>();

		try {
			rs = stmt
					.executeQuery("select meeting.eDate,mTime,gFName,bFname,numOfMeeting from meeting,customer"
							+ " where meeting.cID=customer.cID and meeting.managerIncharge='"
							+ managerID + "' and mDate=current_date");
			while (rs.next()) {
				Massage msg = new Massage();
				msg.setCustName(rs.getString("gFName") + " and "
						+ rs.getString("bFname"));
				msg.seteDate(rs.getString("eDate"));
				msg.setmTime(rs.getString("mTime"));
				msg.setNumOfMeeting(rs.getInt("numOfMeeting"));
				msgList.add(msg);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return msgList;

	}
	/**
	 * this method gets workers that can be schedule to a given date
	 * @param date is the event date 
	 ** @return a list with the workers
	 */
	public Object getWorkerToScheduleQuery(String date){
		ArrayList<User> msgList = new ArrayList<User>();
		try {
		stmt.executeUpdate("create view buzyW as select fname,LName,role,worker.wID from worker,workerinevent where " +
				"workerinevent.wId=worker.wID and workerinevent.eventDate='2014-08-20'");

//			rs = stmt.executeQuery("select * from besyWorkers");
////			if(rs.next()){
//				rs = stmt.executeQuery("select * from worker,besyWorkers where wid<>teamLeader and wid<>stilsPhoto and wid<>vPhotografer1 " +
//					"and wid<>vPhotografer2 and wid<>magnetsPhoto1 and wid<>magnetsPhoto2");
//				System.out.println("view besyWorkers no empty");
//		//	}
//			else{
//				rs = stmt.executeQuery("select * from worker");
//			}
			rs = stmt.executeQuery("select fname,LName,role,wID from worker where wID not in (select wID from buzyW) ");
			while (rs.next()) {
				System.out.println("rs no empty");
				
				User user = new User();
				user.setName(rs.getString("fname"));
				user.setLName(rs.getString("LName"));
				user.setUserName(rs.getInt("wID"));
				user.setRole(rs.getString("role"));
				msgList.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			stmt.executeUpdate("drop view buzyW");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return msgList;
	}
	/**
	 * This method gets all users exists in DB
	 ** @return a list with all user details
	 */
	public Object getAllUsersQuery() {
		ArrayList<User> msgList = new ArrayList<User>();
		try {
			rs = stmt.executeQuery("select * from user,worker where user.userID=worker.wID");
			while (rs.next()) {
				User user = new User();
				user.setName(rs.getString("fname"));
				user.setLName(rs.getString("LName"));
				user.setUserName(rs.getInt("wID"));
				user.setPrivilage(rs.getString("Privilage"));
				user.setAddress(rs.getString("address"));
				user.setPhoneNum(rs.getString("phonenNumber"));
				user.setEmail(rs.getString("Email"));
				user.setRole(rs.getString("role"));

				msgList.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return msgList;
	}
//	public Object getAllPhotographerQuery() {
//		ArrayList<User> msgList = new ArrayList<User>();
//		try {
//			rs = stmt.executeQuery("select * from worker,s where user.userID=worker.wID");
//			while (rs.next()) {
//				User user = new User();
//				user.setName(rs.getString("fname"));
//				user.setLName(rs.getString("LName"));
//				user.setUserName(rs.getInt("wID"));
//				user.setPrivilage(rs.getString("Privilage"));
//				user.setAddress(rs.getString("address"));
//				user.setPhoneNum(rs.getString("phonenNumber"));
//				user.setEmail(rs.getString("Email"));
//				user.setRole(rs.getString("role"));
//
//				msgList.add(user);
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//
//		return msgList;
//	}

	/**
	 * This method gets details from DB abut the amount of events per month 
	 * @param ePerMonth is a Report type entity contain the start and end year and month to create the report
	 * @return a HashMap with the year as the key and the amount of event per month as the value
	 */
	public Object getEventInMonthQuery(Report ePerMonth) {
		System.out.println("in month query  ");
		HashMap<String, Integer[]> hm=new HashMap<>();
		int monthFrom = 0;
		int monthTo = 0;
		switch (ePerMonth.getFromMonth()) {
		case "January":monthFrom = 1;break;
		case "February":monthFrom = 2;break;
		case "March":monthFrom = 3;break;
		case "April":monthFrom = 4;break;
		case "May":monthFrom = 5;break;
		case "June":monthFrom = 6;break;
		case "July":monthFrom = 7;break;
		case "August":monthFrom = 8;break;
		case "September":monthFrom = 9;break;
		case "October":monthFrom = 10;break;
		case "November":monthFrom = 11;	break;
		case "December":monthFrom = 12;break;
		}
		switch (ePerMonth.getToMonth()) {
		case "January":monthTo = 1;break;
		case "February":monthTo = 2;break;
		case "March":monthTo = 3;break;
		case "April":monthTo = 4;break;
		case "May":monthTo = 5;break;
		case "June":monthTo = 6;break;
		case "July":monthTo = 7;break;
		case "August":monthTo = 8;break;
		case "September":monthTo = 9;break;
		case "October":monthTo = 10;break;
		case "November":monthTo = 11;break;
		case "December":monthTo = 12;break;
		}
		Integer[] mCnt = new Integer[12];
		for (int i = 0; i < mCnt.length; i++)
			mCnt[i] = 0;
		try {
			if(ePerMonth.getFromYear().equals(ePerMonth.getToYear())){
			rs = stmt.executeQuery("SELECT EXTRACT(YEAR FROM eDate) AS OrderYear,"+ " EXTRACT(MONTH FROM eDate) AS OrderMonth FROM event where "
							+ "EXTRACT(YEAR FROM eDate)='"+ ePerMonth.getFromYear()+ "' and EXTRACT(MONTH FROM eDate)>='"+ monthFrom+ "' "
							+ "and EXTRACT(MONTH FROM eDate)<='"+ monthTo + "'");
			while (rs.next()) 
				mCnt[rs.getInt("OrderMonth") - 1]++;
			hm.put(ePerMonth.getFromYear(),mCnt);
			}
			else{
				int deff = Integer.parseInt(ePerMonth.getToYear())-Integer.parseInt(ePerMonth.getFromYear());
				
				rs = stmt.executeQuery("SELECT EXTRACT(YEAR FROM eDate) AS OrderYear,"+ " EXTRACT(MONTH FROM eDate) AS OrderMonth FROM event where "
						+ "EXTRACT(YEAR FROM eDate)='"+ ePerMonth.getFromYear()+ "' "+ " and EXTRACT(MONTH FROM eDate)>='"+ monthFrom+ "' "
						+ "and EXTRACT(MONTH FROM eDate)<='12'");
				while (rs.next()) 
					mCnt[rs.getInt("OrderMonth") - 1]++;
				hm.put(ePerMonth.getFromYear(),mCnt);
				System.out.println("deff :"+deff);
				while(deff>1){
					System.out.println("in deff>1");
					Integer temp[] = new Integer[12];
					for(int i= 0; i<mCnt.length;i++)
						temp[i]=0;
					int year = Integer.parseInt(ePerMonth.getFromYear())+1;
					deff--;
					System.out.println("deff :"+deff);
					ePerMonth.setFromYear(String.valueOf(year));
					rs = stmt.executeQuery("SELECT EXTRACT(YEAR FROM eDate) AS OrderYear,"+ " EXTRACT(MONTH FROM eDate) AS OrderMonth FROM event where "
							+ "EXTRACT(YEAR FROM eDate)='"+ ePerMonth.getFromYear()+ "' "+ " and EXTRACT(MONTH FROM eDate)>='1' "
							+ "and EXTRACT(MONTH FROM eDate)<='12'");
					while (rs.next()) 
						temp[rs.getInt("OrderMonth") - 1]++;
					hm.put(ePerMonth.getFromYear(),temp);
				
				}
				Integer year = Integer.parseInt(ePerMonth.getFromYear()) +1;
				System.out.println("year = "+year);
				
				ePerMonth.setFromYear(String.valueOf(year));
				System.out.println("year in entity = "+ePerMonth.getFromYear());
				rs = stmt.executeQuery("SELECT EXTRACT(YEAR FROM eDate) AS OrderYear, EXTRACT(MONTH FROM eDate) AS OrderMonth FROM event where "
						+ "EXTRACT(YEAR FROM eDate)='"+ ePerMonth.getFromYear()+ "'"+ " and EXTRACT(MONTH FROM eDate)>='1'"
						+ " and EXTRACT(MONTH FROM eDate)<='"+monthTo+"'");
				System.out.println(monthTo);
				Integer [] tctn = new Integer[12];
				for(int i= 0; i<tctn.length;i++)
					tctn[i]=0;
				while (rs.next()) {
					System.out.println("in while");
					tctn[rs.getInt("OrderMonth") - 1]++;
				}
				hm.put(ePerMonth.getFromYear(),tctn);
				
			}
			
				
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return hm;
	}
	public Object numberOfMonth(String monthName){
		
		Integer monthNumber=0;
		switch (monthName) 
		{
			case "January":monthNumber = 1;break;
			case "February":monthNumber = 2;break;
			case "March":monthNumber = 3;break;
			case "April":monthNumber = 4;break;
			case "May":monthNumber = 5;break;
			case "June":monthNumber = 6;break;
			case "July":monthNumber = 7;break;
			case "August":monthNumber = 8;break;
			case "September":monthNumber = 9;break;
			case "October":monthNumber = 10;break;
			case "November":monthNumber = 11;	break;
			case "December":monthNumber = 12;break;
		}
		return monthNumber;
	}
//	/**
//	 * This method gets details from DB abut the amount of events per Halls 
//	 * @param hPerMonth is a Report type entity contain the start and end year and month to create the report
//	 * @return a HashMap with the year as the key and the amount of event per month as the halls
//	 */
//	public Object amountOfMettingPerCustomerQuery(Report hPerMonth) {
//		System.out.println("in sql holeEvent");
//		HashMap<String, ArrayList<Report>> hm=new HashMap<>();
//		Integer monthFrom =(Integer) numberOfMonth(hPerMonth.getFromMonth());
//		Integer monthTo =  (Integer) numberOfMonth(hPerMonth.getToMonth());
//		
//		try {
//			if(hPerMonth.getFromYear().equals(hPerMonth.getToYear())){
//				rs = stmt.executeQuery("SELECT eDate,cID,numOfMeeting FROM meeting where "
//							+ "EXTRACT(YEAR FROM eDate)='"+ hPerMonth.getFromYear()+ "' and EXTRACT(MONTH FROM eDate)>='"+ monthFrom+ "' "
//							+ "and EXTRACT(MONTH FROM eDate)<='"+ monthTo + "'");
//				ResultSet tempRS= new 
//				ArrayList<Report> list =new ArrayList<>();
//			while (rs.next()) {
//				System.out.println("in if while");
//				Report r = new Report();
//				r.setObject(rs.getString("eDate"));
//				r.setObjectCnt(rs.getInt("cnt"));
//				list.add(r);
//			}
//			hm.put(hPerMonth.getFromYear(),list);
//			System.out.println("in sql iff");
//			}
//			else{
//				int deff = Integer.parseInt(hPerMonth.getToYear())-Integer.parseInt(hPerMonth.getFromYear());
//				
//				rs = stmt.executeQuery("SELECT ePlace,count(*) as cnt FROM event where "
//						+ "EXTRACT(YEAR FROM eDate)='"+ hPerMonth.getFromYear()+ "' "+ " and EXTRACT(MONTH FROM eDate)>='"+ monthFrom+ "' "
//						+ "and EXTRACT(MONTH FROM eDate)<='12' group by ePlace");
//				ArrayList<Report> list1 = new ArrayList<>();
//				while (rs.next()) {
//					Report r = new Report();
//					r.setObject(rs.getString("ePlace"));
//					r.setObjectCnt(rs.getInt("cnt"));
//					list1.add(r);
//				}
//				hm.put(hPerMonth.getFromYear(),list1);
//				
//				while(deff>1){
//					int year = Integer.parseInt(hPerMonth.getFromYear())+1;
//					deff--;
//					hPerMonth.setFromYear(String.valueOf(year));
//					rs = stmt.executeQuery("SELECT ePlace,count(*) as cnt FROM event where "
//							+ "EXTRACT(YEAR FROM eDate)='"+ hPerMonth.getFromYear()+ "' "+ " and EXTRACT(MONTH FROM eDate)>='1' "
//							+ "and EXTRACT(MONTH FROM eDate)<='12' group by ePlace");
//					ArrayList<Report> list2 = new ArrayList<>();
//					while (rs.next()) {
//						if(rs.getInt("cnt")!=0){
//							System.out.println("in deff>1 while");
//							Report r = new Report();
//							r.setObject(rs.getString("ePlace"));
//							r.setObjectCnt(rs.getInt("cnt"));
//							list1.add(r);
//						}
//						if(!(list2.isEmpty()))
//								hm.put(hPerMonth.getFromYear(),list2);
//					}
//				}
//				Integer year = Integer.parseInt(hPerMonth.getFromYear()) +1;
//				hPerMonth.setFromYear(String.valueOf(year));
//				rs = stmt.executeQuery("SELECT ePlace,count(*) as cnt FROM event where "
//						+ "EXTRACT(YEAR FROM eDate)='"+ hPerMonth.getFromYear()+ "' and EXTRACT(MONTH FROM eDate)>='1'"
//						+ " and EXTRACT(MONTH FROM eDate)<='"+monthTo+"' group by ePlace");
//				ArrayList<Report> list3 = new ArrayList<>();
//				while (rs.next()) {
//					Report r = new Report();
//					r.setObject(rs.getString("ePlace"));
//					r.setObjectCnt(rs.getInt("cnt"));
//					System.out.println(r.getObject());
//					System.out.println(r.getObjectCnt());
//					list3.add(r);
//				}
//				hm.put(hPerMonth.getFromYear(),list3);
//				
//				
//			}
//			
//				
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		return hm;
//	}
//	
	/**
	 * This method gets details from DB abut the amount of events per Halls 
	 * @param hPerMonth is a Report type entity contain the start and end year and month to create the report
	 * @return a HashMap with the year as the key and the amount of event per month as the halls
	 */
	public Object getEventPerHolesQuery(Report hPerMonth) {
		System.out.println("in sql holeEvent");
		HashMap<String, ArrayList<Report>> hm=new HashMap<>();
		System.out.println("go to numberOfManth");
		Integer monthFrom =(Integer) numberOfMonth(hPerMonth.getFromMonth());
		System.out.println("monthFrom: "+monthFrom);
		System.out.println("go to numberOfManth 2");
		Integer monthTo =  (Integer) numberOfMonth(hPerMonth.getToMonth());
		System.out.println("monthTo: "+monthTo);
		
		try {
			if(hPerMonth.getFromYear().equals(hPerMonth.getToYear())){
				System.out.println("from year: "+hPerMonth.getFromYear());
			rs = stmt.executeQuery("SELECT ePlace,count(*) as cnt FROM event where "
							+ "EXTRACT(YEAR FROM eDate)='"+ hPerMonth.getFromYear()+ "' and EXTRACT(MONTH FROM eDate)>='"+ monthFrom+ "' "
							+ "and EXTRACT(MONTH FROM eDate)<='"+ monthTo + "' group by ePlace");
			ArrayList<Report> list =new ArrayList<>();
			while (rs.next()) {
				System.out.println("in if while");
				Report r = new Report();
				r.setObject(rs.getString("ePlace"));
				r.setObjectCnt(rs.getInt("cnt"));
				list.add(r);
			}
			hm.put(hPerMonth.getFromYear(),list);
			System.out.println("in sql iff");
			}
			else{
				int deff = Integer.parseInt(hPerMonth.getToYear())-Integer.parseInt(hPerMonth.getFromYear());
				
				rs = stmt.executeQuery("SELECT ePlace,count(*) as cnt FROM event where "
						+ "EXTRACT(YEAR FROM eDate)='"+ hPerMonth.getFromYear()+ "' "+ " and EXTRACT(MONTH FROM eDate)>='"+ monthFrom+ "' "
						+ "and EXTRACT(MONTH FROM eDate)<='12' group by ePlace");
				ArrayList<Report> list1 = new ArrayList<>();
				while (rs.next()) {
					Report r = new Report();
					r.setObject(rs.getString("ePlace"));
					r.setObjectCnt(rs.getInt("cnt"));
					list1.add(r);
				}
				hm.put(hPerMonth.getFromYear(),list1);
				
				while(deff>1){
					int year = Integer.parseInt(hPerMonth.getFromYear())+1;
					deff--;
					hPerMonth.setFromYear(String.valueOf(year));
					rs = stmt.executeQuery("SELECT ePlace,count(*) as cnt FROM event where "
							+ "EXTRACT(YEAR FROM eDate)='"+ hPerMonth.getFromYear()+ "' "+ " and EXTRACT(MONTH FROM eDate)>='1' "
							+ "and EXTRACT(MONTH FROM eDate)<='12' group by ePlace");
					ArrayList<Report> list2 = new ArrayList<>();
					while (rs.next()) {
						if(rs.getInt("cnt")!=0){
							System.out.println("in deff>1 while");
							Report r = new Report();
							r.setObject(rs.getString("ePlace"));
							r.setObjectCnt(rs.getInt("cnt"));
							list1.add(r);
						}
						if(!(list2.isEmpty()))
								hm.put(hPerMonth.getFromYear(),list2);
					}
				}
				Integer year = Integer.parseInt(hPerMonth.getFromYear()) +1;
				hPerMonth.setFromYear(String.valueOf(year));
				rs = stmt.executeQuery("SELECT ePlace,count(*) as cnt FROM event where "
						+ "EXTRACT(YEAR FROM eDate)='"+ hPerMonth.getFromYear()+ "' and EXTRACT(MONTH FROM eDate)>='1'"
						+ " and EXTRACT(MONTH FROM eDate)<='"+monthTo+"' group by ePlace");
				ArrayList<Report> list3 = new ArrayList<>();
				while (rs.next()) {
					Report r = new Report();
					r.setObject(rs.getString("ePlace"));
					r.setObjectCnt(rs.getInt("cnt"));
					System.out.println(r.getObject());
					System.out.println(r.getObjectCnt());
					list3.add(r);
		
				}
				hm.put(hPerMonth.getFromYear(),list3);
				
				
			}
			
				
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return hm;
	}

	public Object getEvenToScheduleQuery() {

		ArrayList<Schedule> schedList = new ArrayList<>();
		try {
			stmt.executeUpdate("create view eve as select eDate from event,schedule where eDate=schedule.Date and schedule.place=ePlace");
			rs=stmt.executeQuery("select * from eve");
			if(rs.next()){
				rs = stmt.executeQuery("select event.eDate,ePlace, gFname, gLname, bFname, bLname,gPhoneNumber,bPhoneNumber from event,customer,eve "
					+ "where event.cId=customer.cID and event.eDate<>eve.eDate and event.eDate>=current_date()");
			}else{
				rs = stmt.executeQuery("select eDate,ePlace, gFname, gLname, bFname, bLname,gPhoneNumber,bPhoneNumber from event,customer "
						+ "where event.cId=customer.cID and event.eDate>=current_date()");
			}
				while (rs.next()) {
					Schedule s = new Schedule();
					s.setDate(rs.getString("eDate"));
					s.setPlace(rs.getString("ePlace"));
					s.setgContactName(rs.getString("gFname") + " "
							+ rs.getString("gLname"));
					s.setbContactName(rs.getString("bFname") + " "
							+ rs.getString("bLname"));
					s.setgContactPhone(rs.getString("gPhoneNumber"));
					s.setbContactPhone(rs.getString("bPhoneNumber"));
	
					schedList.add(s);
				}
				
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			stmt.executeUpdate("drop view eve");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return schedList;
	}
	/**
	 * 
	 * this method verify if the given random password doesn't exists in DB
	 * @param pass is the password to check
	 * @return true if password doesn't exists and false otherwise
	 * 
	 */
	public Object getVerifyPassQuery(String pass){
		try {
			rs = stmt.executeQuery("select * from user where password='"+ pass + "'");
			if (rs.next())
				return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return true;
	}
	/**
	 * 
	 * this method save the path of a video in DB
	 * @param e is an entity which contain the event date, customer id and the video path
	 * @return true if set video path succeed  and false otherwise
	 * 
	 */
	public Object saveVideoPathQuery(Event e){
		PreparedStatement preparedStatement;
		FileInputStream input=null;
		try {
			preparedStatement = conn.prepareStatement("update event set eVideo=? where eDate= = ? and cID=?");
			
			try {
				input = new FileInputStream(new File(e.getEventvideoPath()));
			} catch (FileNotFoundException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			
			preparedStatement.setBlob(1, input);
			preparedStatement.setString(2, e.getEvetDate());
			preparedStatement.setInt(3, e.getCustomerID());
			preparedStatement.executeUpdate();

			
			
		} catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		}
		return true;
	}
	/**
	 * 
	 * this method get the path of a video from DB
	 * @param e is an entity which contain the event date, customer id
	 * @return the video path 
	 * 
	 */
	public Object getVideoPathQuery(Event e){
		String path=null;
		try {
			rs=stmt.executeQuery("select eVideo from event where eDate='"+e.getEvetDate()+"' and cID='"+e.getCustomerID()+"'");
			if(rs.next())
				path=rs.getString("eVideo");
		} catch (SQLException ex) {
			ex.printStackTrace();
			
		}
		return path;
	}

	/**
	 * 
	 * this method get workers Email 
	 * @param mail is entity that contain all worker's names
	 * @return email entity with theworker's Email and
	 * 
	 */
	public Object getWorkersE_Mail(WorkerEmails mail) {
		String[] worker_name=null;
		try {
			worker_name = mail.getS_name().split(" ");
			rs = stmt.executeQuery("select Email from worker where fName='"+ worker_name[0] + "' and LName='"+ worker_name[1] + "'");
			if (rs.next())
				mail.setS_mail(rs.getString("Email"));
			worker_name = mail.getV1_name().split(" ");
			rs = stmt.executeQuery("select Email from worker where fName='"+ worker_name[0] + "' and LName='"+ worker_name[1] + "'");
			if (rs.next())
				mail.setV1_mail(rs.getString("Email"));
			worker_name = mail.getTeam_name().split(" ");
			rs = stmt.executeQuery("select Email from worker where fName='"+ worker_name[0] + "' and LName='"+ worker_name[1] + "'");
			if (rs.next())
				mail.setTeam_mail(rs.getString("Email"));
			if(!(mail.getM1_name().equals("")))
			{
				worker_name = mail.getM1_name().split(" ");
				rs = stmt.executeQuery("select Email from worker where fName='"+ worker_name[0] + "' and LName='"+ worker_name[1] + "'");
				if (rs.next())
					mail.setM1_mail(rs.getString("Email"));
				worker_name = mail.getM2_name().split(" ");
				rs = stmt.executeQuery("select Email from worker where fName='"+ worker_name[0] + "' and LName='"+ worker_name[1] + "'");
				if (rs.next())
					mail.setM2_mail(rs.getString("Email"));
			}
			if(!(mail.getV2_name().equals("")))
			{
				worker_name = mail.getV2_name().split(" ");
				rs = stmt.executeQuery("select Email from worker where fName='"+ worker_name[0] + "' and LName='"+ worker_name[1] + "'");
				if (rs.next())
					mail.setV2_mail(rs.getString("Email"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return mail;
	}
	/**
	 * 
	 * this method get customer's and manager incharge's Email and meeting time
	 * and date
	 * @param mail is entity that contain both customer's and manager incharge's names
	 * @return email entity with the customer's and manager incharge's Email and
	 *  meeting time and date
	 * 
	 */
	public Object getE_Mail(E_Mail mail) {
		Integer mID = 0;
		try {
			String[] manager_name = mail.getM_Incharge().split(" ");
			rs = stmt.executeQuery("select wID from worker where fName='"+ manager_name[0] + "' and LName='"+ manager_name[1] + "'");
			if (rs.next())
				mID = rs.getInt("wID");
			rs = stmt
					.executeQuery("select customer.gFname, customer.gLname,customer.bFname, customer.bLname, customer.Email as c_mail, worker.Email as m_mail, meeting.mDate, meeting.mTime"
							+ " from meeting, customer,worker where meeting.cID=customer.cID and"
							+ " meeting.managerIncharge=worker.wID and customer.cID='"+ mail.getcID()+ "'"
							+ " and worker.wID='"+ mID+ "' ");
			if (rs.next()) {
				mail.setC_mail(rs.getString("c_mail"));
				mail.setM_mail(rs.getString("m_mail"));
				mail.setmDate(rs.getString("mDate"));
				mail.setmTime(rs.getString("mTime"));
				mail.setbName(rs.getString("bFname")+" "+ rs.getString("bLname"));
				mail.setgName(rs.getString("gFname")+" "+ rs.getString("gLname"));
			}
			else return null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return mail;
	}
}
